package db;

import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.Format;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.UUID;
import java.sql.SQLException;


import javax.servlet.http.Cookie;

import com.mysql.jdbc.CallableStatement;

@SuppressWarnings("unused")
public class CopyOfDBHelper {

	
	// *************** CLASS VARIABLES ******************************************************************* 
	
	/** Database connection variable */
	Connection conn;

	
	// *************** PREPARED STATEMENT VARIABLES *******************************************************
	
	// *************** USER ****************** 
	
	/** Statement to validate user login */
	PreparedStatement validateUserStatement;
	
	/** Statement to validate user does not already exist */
	PreparedStatement validateUserDoesNotExistStatement;
	
	/** Statement to add new user */
	PreparedStatement registerUserStatement;
	
	/** Statement to get user data */
	PreparedStatement userDataStatement;
	
	/** Statement to get coupons or rewards data */
	PreparedStatement getRewardsDataStatement;
	
	/** Statement to get coupons data */
	PreparedStatement getCouponsDataStatement;
	
	/** Statement to get role data */
	PreparedStatement roleDataStatement;
	
	/** Statement check user out */
	PreparedStatement checkUserOut;
	
	/** Statement to check user in */
	PreparedStatement checkUserInStatement;
	
	/** Statement to validate user is not checked in already */
	PreparedStatement validateIfUserIsCheckedInStatement;
	
	/** Statement to update user data */
	PreparedStatement updateUserDataStatement;
	
	/** Statement de-activate a user */
	PreparedStatement deleteUserStatement;
	
	
	// *************** STORE ****************** 
	
	/** Statement to get stores data */
	PreparedStatement storeDataStatement;
	
	/** Statement to gets users checked in by store */
	PreparedStatement getUserCheckedInByStoreDataStatement;
	
	/** Statement get a user checked in by store */
	PreparedStatement getIndivUserCheckedInByDataStatement;
	
	/** Statement to complete check out */
	CallableStatement completeCheckOut;
	
	/** Statement to add reward to check in table data */
	PreparedStatement addSelectedRewardStatement;
	
	/** Statement get check in location data */
	PreparedStatement getCheckInLocationByCustIDStatement;
	
	/** Statement reset check in location data */
	PreparedStatement resetCheckInLocationStatement;

	
	// *************** COUPON ****************** 
	
	/** Statement get a coupon */
	PreparedStatement getCouponStatement;
	
	/** Statement add a coupon */
	PreparedStatement addCouponStatement;
	
	/** Statement update a coupon */
	PreparedStatement updateCouponStatement;
	
	/** Statement delete a coupon */
	PreparedStatement deleteCouponStatement;
	
	
	// *************** CAMPAIGN ****************** 
	
	/** Statement add a Campaign */
	PreparedStatement addCampaignStatement;
	
	/** Statement update a Campaign */
	PreparedStatement updateCampaignStatement;
	
	/** Statement delete a Campaign */
	PreparedStatement deleteCampaignStatement;
	
	/** Statement get all Campaign */
	PreparedStatement getCampaignsStatement;
	
	/** Statement get a Campaign */
	PreparedStatement getCampaignDataStatement;
	
	/** Statement get all customers */
	PreparedStatement getCustomersStatement;
	
	/** Statement search for customers */
	PreparedStatement searchCustomersDataStatement;
	
	String err = "";
	
	public String getError() {
		return err;
	}
		
	// ************ CONSTRUCTOR ******************************************************************
	
	/**
	 * Constructor creates connection to MySQL server and creates prepared statements 
	 */
	public CopyOfDBHelper() {

		/*
		 * Set database parameters, establish connection, and create prepared
		 * statements
		 */
		
		String db_url = "jdbc:mysql://ec2-50-19-53-184.compute-1.amazonaws.com:3306/frontera?noAccessToProcedureBodies=true";
		String db_uname = "root";
		String db_password = "fr0nt3ra";

		/*
		 * if test is running check if contains test database connection
		 * properties and use
		 */
		if (System.getProperty("db_url") != null) {
			db_url = System.getProperty("db_url");
			db_uname = System.getProperty("db_uname");
			db_password = System.getProperty("db_password");
		}

		try {
			
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			java.sql.Connection conn = DriverManager.getConnection(db_url,
					db_uname, db_password);

			System.out.println("Connected to MySQL!");

			// create the prepared statements separated for readability
			createPreparedStatements(conn);

		} catch (Exception e) {
			System.out.println("Error in DBHelper constructor: "
					+ e.getClass().getName() + ": " + e.getMessage());
			err = e.getClass().getName() + ": " + e.getMessage();
		}

	}
	
	private void connection (){
		
	}
	
	// *************** PREPARED STATEMENTS *******************************************************
	/**
	 * create the prepared statements separated for readability 
	 */
	private void createPreparedStatements(Connection connReceived) {
		
		Connection conn = connReceived;
		
	try{


		
	// *************** USER ****************** 
		
			// returns 1 if login valid, returns 0 if login not valid
			validateUserStatement = conn
					.prepareStatement("SELECT IFNULL(COUNT(*),0) AS userLoginValid  FROM Customer "
							+ " where password = ? and PhoneNumber = ?");

			// returns 1 if user exists, returns 0 if user does not exist
			validateUserDoesNotExistStatement = conn
					.prepareStatement("SELECT IFNULL(COUNT(*),0) AS UserExist  FROM Customer "
							+ " where PhoneNumber = ? ");

			registerUserStatement = conn
					.prepareStatement("INSERT INTO Customer(Name,PhoneNumber,EmailAddress,Password,birthDay,RoleTypes_idRoleTypes, CreatedDatetime, ModifiedDatetime)"
							+ " VALUES(?,?,?,?,?,?,?,?);");

			// TODO: add a join to query for user location,total peso and
			// rewards
			userDataStatement = conn
					.prepareStatement("SELECT * FROM Customer WHERE PhoneNumber = ?");

			String sDate = "2013-02-11 23:59:59";
			String eDate = "2013-03-11 23:59:59";
			String pointValue = "205";
			String qryStringSdates = " and active = 0  and begindatetime >= '"
					+ sDate + "'";
			String qryStringEdates = " and enddatetime <= '" + eDate + "'";
			String qryStringPointValues = " and pointvalue between 1 and "
					+ pointValue;

			String defaultqryStringSdates = " and active = 0  and begindatetime >= begindatetime";
			String defaultqryStringEdates = " and enddatetime <= enddatetime";
			String defaultqryStringPointValues = " and pointvalue between 1 and 200 ";

			getRewardsDataStatement = conn
					.prepareStatement("SELECT * FROM Coupons where 1 = 1 "
							+ qryStringSdates + qryStringEdates
							+ qryStringPointValues + " order by idCoupons asc");

			getCouponsDataStatement = conn
					.prepareStatement("SELECT * FROM Coupons where 1 = 1");

			roleDataStatement = conn
					.prepareStatement("SELECT * FROM RoleTypes");

			checkUserOut = conn
					.prepareStatement("delete from CheckInOut where idCheckInOut = ?;");

			checkUserInStatement = conn
					.prepareStatement("INSERT INTO CheckInOut"
							+ "(Customer_idCustomer,"
							+ "Store_idStore,CheckInOut)" + "VALUES(?,?,0);");

			validateIfUserIsCheckedInStatement = conn
					.prepareStatement("SELECT count(*) "
							+ "as exist FROM CheckInOut where Customer_idCustomer = ? and CheckInOut = 0;");

			updateUserDataStatement = conn.prepareStatement("UPDATE Customer "
					+ "SET Name = ? " + ",PhoneNumber = ? "
					+ ",EmailAddress = ? " + ",Password = ? "
					+ ",birthDay = ? " + ",RoleTypes_idRoleTypes = ? "
					+ "  where idCustomer = ? ");

			deleteUserStatement = conn
					.prepareStatement("update Customer set Active = ? where idCustomer = ?;");
			
			
	// *************** STORE ****************** 
			
			storeDataStatement = conn.prepareStatement("SELECT * FROM Store");

			getUserCheckedInByStoreDataStatement = conn
					.prepareStatement("SELECT cko.Customer_idCustomer,cko.Store_idStore, "
							+ "		cko.idCheckInOut,cko.Coupons_idCoupons,cus.Name cusName,cus.phoneNumber,cus.EmailAddress, "
							+ "		str.Name storeName, cup.Name couponName, cup.PointValue "
							+ "		FROM CheckInOut  cko "
							+ "		left join Customer cus "
							+ "		on cus.idCustomer  = cko.Customer_idCustomer "
							+ "		left join Coupons cup  "
							+ "		on cup.idCoupons = cko.Coupons_idCoupons "
							+ "		left join Store str "
							+ "		on str.idStore = cko.Store_idStore "
							+ "		where  cko.CheckInOut = 0 and str.idStore = ?");

			getIndivUserCheckedInByDataStatement = conn
					.prepareStatement("SELECT cko.Customer_idCustomer,cko.Store_idStore,"
							+ "cko.idCheckInOut,cko.Coupons_idCoupons,cus.Name cusName,cus.phoneNumber,cus.EmailAddress,"
							+ "		str.Name storeName, cup.Name couponName, cup.PointValue FROM CheckInOut  cko, Customer cus, Coupons cup ,Store str"
							+ "		where cus.idCustomer  = cko.Customer_idCustomer and cup.idCoupons = cko.Coupons_idCoupons"
							+ "		and str.idStore = cko.Store_idStore and cko.idCheckInOut = ?");
			
			// STORED PROCEDURE CALL
			completeCheckOut = (CallableStatement) conn
					.prepareCall("{call ledgerDo(?,?,?,?)}");

			addSelectedRewardStatement = conn
					.prepareStatement("UPDATE CheckInOut "
							+ "SET Coupons_idCoupons = ? "
							+ "WHERE idCheckInOut = ? ");

			getCheckInLocationByCustIDStatement = conn
					.prepareStatement("SELECT cko.Customer_idCustomer,cko.Store_idStore,"
							+ "cko.idCheckInOut,cko.Coupons_idCoupons "
							+ "		 FROM CheckInOut  cko "
							+ "		where cko.Customer_idCustomer = ? and CheckInOut = 0;");

			resetCheckInLocationStatement = conn
					.prepareStatement("delete from CheckInOut where Customer_idCustomer = ?;");
			
			
	// *************** COUPON ****************** 
		
			getCouponStatement = conn
					.prepareStatement("SELECT * from Coupons where idCoupons = ? ");

			addCouponStatement = conn
					.prepareStatement("INSERT INTO Coupons( Name,PointValue,DollarOffValue,BeginDatetime,EndDatetime,Active,CreatedBy,birthdayPromo) "
							+ "VALUES(?,?,?,?,?,?,?,?);");

			updateCouponStatement = conn
					.prepareStatement("UPDATE Coupons  SET Name = ?,"
							+ " PointValue = ?,DollarOffValue = ?,BeginDatetime = ?,EndDatetime = ?,Active = ?,"
							+ " ModifiedBy = ? ,ModifiedDatetime = current_timestamp, birthdayPromo = ? WHERE idCoupons = ?;");

			deleteCouponStatement = conn
					.prepareStatement("delete from Coupons where idCoupons = ? ");

			
	// *************** CAMPAIGN ****************** 

			addCampaignStatement = conn
					.prepareStatement("INSERT INTO Campaign( Name,Description,PointMultiplier,BeginDatetime,EndDatetime,Active,CreatedBy,ModifiedBy,birthdayPromo) "
							+ "VALUES(?,?,?,?,?,?,?,?,?);");

			updateCampaignStatement = conn
					.prepareStatement("UPDATE Campaign  SET Name = ?,"
							+ " Description = ?,PointMultiplier = ?,BeginDatetime = ?,EndDatetime = ?,Active = ?,"
							+ " ModifiedBy = ?,ModifiedDatetime = current_timestamp ,birthdayPromo = ? WHERE idCampaign = ?;");

			deleteCampaignStatement = conn
					.prepareStatement("delete from Campaign where idCampaign = ? ");

			getCampaignsStatement = conn
					.prepareStatement("SELECT * FROM Campaign where 1 = 1");

			getCampaignDataStatement = conn
					.prepareStatement("SELECT * from Campaign where idCampaign = ? ");

			getCustomersStatement = conn
					.prepareStatement("SELECT * FROM Customer where 1 = 1");

			searchCustomersDataStatement = conn
					.prepareStatement("SELECT * FROM Customer WHERE (PhoneNumber = ? or emailAddress = ?)");
		
	}
	catch (Exception e){
		System.out.println("Error in constructor: "+e.getClass().getName()+":"+ e.getMessage());
	}		

}
	

	/**
	 * search for customer ID
	 * 
	 * @param phoneNumber
	 * @param emailAddress
	 */
	public int searchCustomerData(String phoneNumber, String emailAddress) {
		int userId = -1;

		String cleanPhoneNumber = "";

		// remove everything but the numbers
		cleanPhoneNumber = phoneNumber.replaceAll("[^0-9]", "");

		// validate there are ten digits
		if (cleanPhoneNumber.length() == 10) {

			System.out.println(cleanPhoneNumber);

			try {
				searchCustomersDataStatement.setString(1, cleanPhoneNumber);
				searchCustomersDataStatement.setString(2, emailAddress);

				System.out
						.println("run validateUserLogin query search customer");
				ResultSet rs = searchCustomersDataStatement.executeQuery();
				while (rs.next()) {
					userId = rs.getInt("idCustomer");

				}
				rs.close();

			}

			catch (Exception e) {
				System.out.println("Error in searchCustomersDataStatement: "
						+ e.getClass().getName() + ":" + e.getMessage());
				userId = -2;
				
			}
		}
		return userId;
	}
	
	
	
	
	                                     

		/** Check the user out 
		 * @param int checkInID
		 * @param String subTotal
		 * @param String ticketId 
		 * @param int customerID 
		 * @param  serverID
		 *		 
		 */
	public int checkUserOut(int checkInID, String subTotal, String ticketId,int customerID, int serverID){
		int intCheckUserOut = 0;
		try{
			
			System.out.println(checkInID);
			System.out.println(subTotal);
			System.out.println(ticketId);
			System.out.println(customerID);
			System.out.println(serverID);
			System.out.println("insert check out");
			
			completeCheckOut.setInt("CustomerID", customerID);
			completeCheckOut.setDouble("rSubtotal", 50);
			completeCheckOut.setString("rNumber", ticketId);
			completeCheckOut.setInt("modBy", serverID);
			ResultSet rs = completeCheckOut.executeQuery();
							
			rs.close();
			checkUserOut.setInt(1, checkInID);
			
			checkUserOut.executeUpdate();
			
		}
	
		catch (Exception e) {
			
			System.out.println("Error in check user out: "+ e.getClass().getName()+":"+e.getMessage());
			intCheckUserOut = 9;
		}
		return intCheckUserOut;
	}
	
	
	public static Cookie eraseCookie(String strCookieName, String strPath) {
	    Cookie cookie = new Cookie(strCookieName, "");
	    cookie.setMaxAge(0);
	    cookie.setPath(strPath);

	    return cookie;
	}
	/** Returns the user data */
	public ArrayList<UserData> getUserInfo (String userID){
		ArrayList<UserData> userInfoData = new ArrayList<UserData>();
		try {
			System.out.println("start query");
			userDataStatement.setString(1, FormatAndValidation.cleanPhoneNumber(userID));
			ResultSet rs = userDataStatement.executeQuery();
			System.out.println("display id " + FormatAndValidation.cleanPhoneNumber(userID));
			while (rs.next()){
				System.out.println("display query");				
			//	`customer`.`idCustomer`, `customer`.`Name`, `customer`.`PhoneNumber`, `customer`.`EmailAddress`, `customer`.`Password`,userActive
				//`customer`.`CreatedDatetime`, `customer`.`ModifiedDatetime`, `customer`.`Active`
				
				UserData newUserData = new UserData(rs.getString("PhoneNumber"),rs.getString("Name"),rs.getString("phoneNumber"),rs.getInt("idCustomer"),rs.getString("EmailAddress"),45,rs.getString("Password"),rs.getString("birthDay"),rs.getInt("Active"),rs.getInt("RoleTypes_idRoleTypes"));
				userInfoData.add(newUserData);
			}
			rs.close();
	}
		
		catch (Exception e){
			System.out.println("Error in userDataStatement: "+e.getClass().getName()+":"+ e.getMessage());
		    }
		System.out.println("end query");
		return userInfoData;
	}
	/** Returns the all Customer info data */
	public ArrayList<UserData> getAllCustomerInfo(){
		ArrayList<UserData> customerData = new ArrayList<UserData>();
		try {
			System.out.println("start query");
			ResultSet rs = getCustomersStatement.executeQuery();
			while (rs.next()){
				System.out.println("display query");				
			//	`customer`.`idCustomer`, `customer`.`Name`, `customer`.`PhoneNumber`, `customer`.`EmailAddress`, `customer`.`Password`,
				//`customer`.`CreatedDatetime`, `customer`.`ModifiedDatetime`, `customer`.`Active`
				
				UserData newUserData = new UserData(rs.getString("PhoneNumber"),rs.getString("Name"),rs.getString("phoneNumber"),rs.getInt("idCustomer"),rs.getString("EmailAddress"),45+rs.getInt("idCustomer"),rs.getString("Password"),rs.getString("birthDay"),rs.getInt("Active"),rs.getInt("RoleTypes_idRoleTypes"));
				customerData.add(newUserData);
			}
			rs.close();
	}
		
		catch (Exception e){
			System.out.println("Error in getAllCustomerInfo: "+e.getClass().getName()+":"+ e.getMessage());
		    }
		System.out.println("end query");
		return customerData;
	}
	
	
	
	/** Returns the store data */
	public ArrayList<StoreLocation> getStoreInfo (){
		ArrayList<StoreLocation> storeInfoData = new ArrayList<StoreLocation>();
		try {
			System.out.println("start store query");
			
			ResultSet rs = storeDataStatement.executeQuery();
			while (rs.next()){
				System.out.println("display  store query");				
			

				StoreLocation newStoreData = new StoreLocation(rs.getString("Name"),rs.getString("Address1"),rs.getString("Address2"),rs.getString("City"),rs.getString("Zipcode"),rs.getString("PhoneNumber"),rs.getString("State"),"45",rs.getInt("idStore"),"1");
				storeInfoData.add(newStoreData);
			}
			rs.close();
	}
		
		catch (Exception e){
			System.out.println("Error in storeDataStatement: "+e.getClass().getName()+":"+ e.getMessage());
		    }
		System.out.println("end store query");
		return storeInfoData;
	}
	
	/** De-activate user data */
	public int deactivateUser(int custID,int activeStatus){
		int validate = 0;
		try{
			//TODO: change Active status to 1 and deactivate status to 0 in the Database
			
			deleteUserStatement.setInt(1, activeStatus);
			deleteUserStatement.setInt(2, custID);
			
						
			deleteUserStatement.executeUpdate();
			
			System.out.println(validate);
		
		}
	
		catch (Exception e) {
			
			System.out.println("Error in deleteUserStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		}
		return validate;
	}
	
	/** Returns a list of users that are checked in by store data */
	public ArrayList<UserCheckInData> getCheckedInUsersinfo (int storeID){
		ArrayList<UserCheckInData> CheckedInUsersInfoData = new ArrayList<UserCheckInData>();
		try {
			System.out.println("start query");
			getUserCheckedInByStoreDataStatement.setInt(1, storeID);
			
			ResultSet rs = getUserCheckedInByStoreDataStatement.executeQuery();
			System.out.println("display checked in users " );
			ArrayList<UserReward> userRewd = new ArrayList<UserReward>();
			int psTotal = 45;
			while (rs.next()){
				System.out.println("display query" +rs.getString("PointValue"));				
				
				UserCheckInData newCheckInData = new UserCheckInData(rs.getString("phoneNumber"),rs.getInt("Customer_idCustomer"),rs.getString("cusName"),rs.getString("EmailAddress")
						,rs.getString("phoneNumber"),rs.getInt("idCheckInOut"),userRewd,psTotal,"no image",rs.getString("storeName")
						,rs.getInt("Store_idStore"),rs.getString("couponName"),rs.getInt("Coupons_idCoupons"),rs.getInt("PointValue"));
				
				 CheckedInUsersInfoData.add(newCheckInData);
			}
			rs.close();
	}
		
		catch (Exception e){
			System.out.println("Error in getUserCheckedInByStoreDataStatement: "+e.getClass().getName()+":"+ e.getMessage());
		    }
		System.out.println("end query");
		return CheckedInUsersInfoData;
	}
	
	/** Returns a specific users by ID that's checked in data */
	public ArrayList<UserCheckInData> getIndivCheckedInUserInfo (int checkInID){
		ArrayList<UserCheckInData> CheckedInUsersInfoData = new ArrayList<UserCheckInData>();
		try {
			System.out.println("start query");
			getIndivUserCheckedInByDataStatement.setInt(1, checkInID);
			
			ResultSet rs = getIndivUserCheckedInByDataStatement.executeQuery();
			System.out.println("display checked in users " );
			ArrayList<UserReward> userRewd = new ArrayList<UserReward>();
			int psTotal = 45;
			while (rs.next()){
				System.out.println("display query" +rs.getString("PointValue"));				
				
				UserCheckInData newCheckInData = new UserCheckInData(rs.getString("phoneNumber"),rs.getInt("Customer_idCustomer"),rs.getString("cusName"),rs.getString("EmailAddress")
						,rs.getString("phoneNumber"),rs.getInt("idCheckInOut"),userRewd,psTotal,"no image",rs.getString("storeName")
						,rs.getInt("Store_idStore"),rs.getString("couponName"),rs.getInt("Coupons_idCoupons"),rs.getInt("PointValue"));
				
				 CheckedInUsersInfoData.add(newCheckInData);
			}
			rs.close();
	}
		
		catch (Exception e){
			System.out.println("Error in getIndivUserCheckedInByDataStatement: "+e.getClass().getName()+":"+ e.getMessage());
		    }
		System.out.println("end query");
		return CheckedInUsersInfoData;
	}
	/** get Customer Store CheckIn Location by using customer id  */
	public int getCustStoreCheckInLocation(int custID){
		int validate = 0;
		try{
			
			getCheckInLocationByCustIDStatement.setInt(1, custID);
			
			ResultSet rs = getCheckInLocationByCustIDStatement.executeQuery();
			while (rs.next()){
				validate = rs.getInt("Store_idStore");//get store id
				System.out.println(" in the process "+      rs.getInt("Store_idStore"));
			}
			System.out.println(validate);
			rs.close();
			
		}
	
		catch (Exception e) {
			
			System.out.println("Error in getCheckInLocationStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		}
		return validate;
	}
	/** get check in ID using cust ID*/
	public int getCustCheckInID(int custID){
		int validate = 0;
		try{
			
			getCheckInLocationByCustIDStatement.setInt(1, custID);
			
			ResultSet rs = getCheckInLocationByCustIDStatement.executeQuery();
			while (rs.next()){
				validate = rs.getInt("idCheckInOut");//get checkin id
				System.out.println(" in the process "+      rs.getInt("idCheckInOut"));
			}
			System.out.println(validate);
			rs.close();	

		}
	
		catch (Exception e) {
			
			System.out.println("Error in getCustCheckInID: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		}
		return validate;
	}
	
	/** get current user check in data */
		public ArrayList<UserCheckInData> getUserCheckInData(int custID){
		int validate = 0;
		ArrayList<UserCheckInData> CheckedInUsersInfoData = new ArrayList<UserCheckInData>();
		try{
			
			getCheckInLocationByCustIDStatement.setInt(1, custID);
			
			ResultSet rs = getCheckInLocationByCustIDStatement.executeQuery();
			while (rs.next()){
				//validate = rs.getInt("Store_idStore");//get store id
				
				UserCheckInData newCheckInData = new UserCheckInData(rs.getInt("Customer_idCustomer"),rs.getInt("idCheckInOut"),rs.getInt("Store_idStore"),rs.getInt("Coupons_idCoupons"));
				
				CheckedInUsersInfoData.add(newCheckInData);
				System.out.println(" in the process ");
			}
			rs.close();	

		}
	
		catch (Exception e) {
			
			System.out.println("Error in getCheckInLocationByCustIDStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		}
		return CheckedInUsersInfoData;
	}
	
	/** check if user is already checked in */
	public int validateIfUserIsCheckedIn(int custID){
		int validate = 0;
		try{
			
			validateIfUserIsCheckedInStatement.setInt(1, custID);
			
			ResultSet rs = validateIfUserIsCheckedInStatement.executeQuery();
			while (rs.next()){
				validate = rs.getInt("exist");//see is userExist already
				System.out.println(" in the process "+      rs.getInt("exist"));
			}
			System.out.println(validate);
			rs.close();
			
		}
	
		catch (Exception e) {
			
			System.out.println("Error in validateIfUserIsCheckedInStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		}
		return validate;
	}
	/** reset check in location*/
	public int resetCheckInLocation(int custID){
		int validate = 0;
		try{
			
			resetCheckInLocationStatement.setInt(1, custID);
			
			resetCheckInLocationStatement.executeUpdate();
			
			System.out.println(validate);

		}
	
		catch (Exception e) {
			
			System.out.println("Error in resetCheckInLocationStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		}
		return validate;
	}
	/** check user in */
	public int checkUserIn(int custID, int storeID){
		int validate = 0;
		try{
			checkUserInStatement.setInt(1, custID);
			checkUserInStatement.setInt(2, storeID);
			
			validate = validateIfUserIsCheckedIn(custID);
			
			if(validate == 0)
				{
			
					System.out.println(custID);
					System.out.println(storeID);
					System.out.println("larry test");
					checkUserInStatement.executeUpdate();
				}
			else
			{
				validate = 10;
			}

		}
	
		catch (Exception e) {
			
			System.out.println("Error in checkUserInStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		}
		return validate;
	}
	
	/** add the user selected reward */
	public int addSelectedReward(int coupID, int checkInID){
		int validate = 0;
		try{
			
			addSelectedRewardStatement.setInt(1, coupID);
			addSelectedRewardStatement.setInt(2, checkInID);
			
			System.out.println(coupID);
			System.out.println(checkInID);
			
			System.out.println("larry test checkInID");
			addSelectedRewardStatement.executeUpdate();
			
		}
	
		catch (Exception e) {
			
			System.out.println("Error in addSelectedRewardStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		}
		return validate;
	}
	
	
	/** Update the user data */
	public int updateUserData(String fullName, String userPhoneNumber, String userEmailAddress,
	   		 String userPassword, String userBirthDay, int custID,int role){
		//role 3=mgmt 2=server 1=customer
		int validate = 0;
		try{
			
			System.out.println(fullName);
			System.out.println(userPhoneNumber);
			System.out.println(userEmailAddress);	
			System.out.println(userPassword);
			System.out.println("update user test");
			System.out.println(userBirthDay);
			System.out.println(role);
			System.out.println(custID);
							
				updateUserDataStatement.setString(1, fullName);
				updateUserDataStatement.setString(2, userPhoneNumber);
				updateUserDataStatement.setString(3, userEmailAddress);
				updateUserDataStatement.setString(4, userPassword);
				updateUserDataStatement.setString(5, userBirthDay);
				updateUserDataStatement.setInt(6, role);
				updateUserDataStatement.setInt(7, custID);
				updateUserDataStatement.executeUpdate();
				
		}
	
		catch (Exception e) {
			
			System.out.println("Error in updateUserDataStatement: "+ e.getClass().getName()+":"+e.getMessage());
			validate = 10;
		}
		return validate;
	}
	
	

	

	
	
}
